package com.conversationboard.batch.jobs.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.mail.MessagingException;
import javax.naming.NamingException;

import org.joda.time.DateTime;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import com.conversationboard.config.Configuration;
import com.conversationboard.database.BatchQueries;
import com.conversationboard.database.Database;
import com.conversationboard.database.Queries;
import com.conversationboard.email.Mail;
import com.conversationboard.logger.LogMessageType;
import com.conversationboard.logger.Logger;
import com.conversationboard.model.BatchMessage;
import com.conversationboard.model.ExistingUsers;

public class UserHousekeeping implements Job {

	private void deleteDeadUsers() throws JobExecutionException {

		try {

			long startTime = System.currentTimeMillis();
			Connection connection = null;

			try {

				connection = Database.getConnection();
				connection.setAutoCommit(false);

				List<String> deadUsers = new LinkedList<String>();

				/* 1. Get all the dead users into a list */

				PreparedStatement statement = connection.prepareStatement(Queries.GET_DEAD_USERS);
				statement.execute();

				ResultSet resultSet = statement.getResultSet();

				while (resultSet.next()) {
					String loginId = resultSet.getString("loginid");
					deadUsers.add(loginId);
				}

				statement.close();

				/* 2. Delete them from the users and the userroles table */

				PreparedStatement deleteUserStatement = connection.prepareStatement(Queries.DELETE_USER);

				for (String loginId : deadUsers) {
					deleteUserStatement.setString(1, loginId);
					deleteUserStatement.addBatch();
				}

				deleteUserStatement.executeBatch();

				PreparedStatement deleteUserRolesStatement = connection.prepareStatement(Queries.DELETE_USERROLE);

				for (String loginId : deadUsers) {
					deleteUserRolesStatement.setString(1, loginId);
					deleteUserRolesStatement.addBatch();
				}

				deleteUserRolesStatement.executeBatch();

				connection.commit();

				long timeTaken = System.currentTimeMillis() - startTime;
				Logger.log(new Date(), "User Housekeeping Job took: " + timeTaken + " milliseconds", null, LogMessageType.DEBUG);

			} catch (SQLException e) {
				connection.rollback();
				throw e;
			} finally {
				Database.close(connection);
			}

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		}

	}


	private void clearOldSuggestBans() throws SQLException {

		Connection connection = null;

		try {
			connection = Database.getConnection();

			DateTime today = new DateTime();
			DateTime oneYearAgo = today.minusYears(1);

			PreparedStatement statement = connection.prepareStatement(BatchQueries.DELETE_OLD_SUGGEST_BANS);
			statement.setDate(1, new java.sql.Date(oneYearAgo.toDate().getTime()));

			statement.executeUpdate();

			statement.close();

		} finally {
			Database.close(connection);
		}
	}


	/**
	 * Look at users registered three days ago (give them time to get their email and change their password to their preference)...
	 * Check if they are sockpuppets (password) and report on them to the site admins if they are.
	 */

	private void reportOnSockpuppets() throws SQLException, MessagingException, NamingException {

		Connection connection = null;
		String[] passwords = {};

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(Queries.GET_RECENTLY_REGISTERED_USERS_PASSWORDS);

			statement.execute();

			ResultSet resultSet = statement.getResultSet();
			List<String> passwordsList = new ArrayList<String>();

			while (resultSet.next()) {
				passwordsList.add(resultSet.getString("password"));
			}

			passwords = passwordsList.toArray(passwords);

		} finally {
			Database.close(connection);
		}

		/* Now check for sockpuppets */

		List<String> existingUsers = ExistingUsers.getExistingUsers(passwords);

		StringBuilder buffer = new StringBuilder();

		buffer.append("A new user has registered that is a sockpuppet of these users:\n\n");

		for (String existingUser : existingUsers) {
			buffer.append(existingUser + "\n");
		}

		String emailContent = buffer.toString();

		Mail mail = new Mail();
		mail.sendToSiteAdmins("Sockpuppets Detected", emailContent);

	}


	public void execute(JobExecutionContext context) throws JobExecutionException {

		try {

			BatchMessage.setMessage("User Housekeeping", true);

			if (Configuration.getInstance().isDeleteOldUsers()) {
				this.deleteDeadUsers();
			}

			this.clearOldSuggestBans();

			if (Configuration.getInstance().isReportOnSockpuppets()) {
				this.reportOnSockpuppets();
			}

		} catch (SQLException e) {
			throw new JobExecutionException(e);
		} catch (MessagingException e) {
			throw new JobExecutionException(e);
		} catch (NamingException e) {
			throw new JobExecutionException(e);
		} finally {
			BatchMessage.clearMessage();
		}

	}

}
